package com.vinhvt.effiport.util;

import java.io.*;
import java.util.*;

import java.sql.*;
import java.text.*;


import com.vinhvt.effiport.data.*;
import com.vinhvt.effiport.finance.*;
import com.vinhvt.effiport.financial.ep.Organization;
import com.vinhvt.effiport.financial.ep.Transaction;

public class DbImporter {		
	private static final String CREATE_ORGANIZATION = "INSERT INTO organization(symbol, name, listing_volume, outstanding_volume, ipo_date) VALUES(?,?,?,?,?)";
	private static final String CREATE_TRANSACTION = "INSERT INTO transaction(symbol, transaction_date, opened, highest, lowest, closed, volume) VALUES(?,?,?,?,?,?,?)";
					
	public void importTransaction(String fileName){
		BufferedReader br = null;		
		Connection connection = null;
		PreparedStatement pstmt = null;
		
		String username = "root";
		String password = "";
		String database = "effiport";
		
		//MySQL Connection
		ConnectionManager cm = new ConnectionManagerMySQL(database, username, password);
		connection = cm.getConnection();
		try {
			pstmt = connection.prepareStatement(CREATE_TRANSACTION);
		} catch (SQLException e2) {			
			e2.printStackTrace();
		}
		
		try {
			File file = new File(fileName);
			FileInputStream fileStream = new FileInputStream(file);
			InputStreamReader reader = new InputStreamReader(fileStream);
			br = new BufferedReader(reader);
			//br = new BufferedReader(new FileReader(this.fileName));
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		StringTokenizer st = null;
		String tmp = null;
		try {			
			while((tmp = br.readLine()) != null){
				st = new StringTokenizer(tmp,",");						
				if(st.countTokens() == 9){
					try{						
						
						java.util.Date date = null;											
						String ticker = st.nextToken();	//1.Ticker						
						st.nextToken(); // 2.PER
						
						DateFormat df = new SimpleDateFormat("yyyyMMdd");
						
						try{							
							date = (java.util.Date) df.parse(st.nextToken());	//3.DATE							
						}
						catch (Exception e) {
							// TODO: handle exception
							e.printStackTrace();
						}
												
						st.nextToken(); //4.TIME
						double openedPrice = Double.valueOf(st.nextToken()); //5.OPEN
						double highestPrice = Double.valueOf(st.nextToken()); //6.HIGH
						double lowestPrice = Double.valueOf(st.nextToken()); //7.LOW						
						double closedPrice = Double.valueOf(st.nextToken()); //8.CLOSE
						long volume = Long.valueOf(st.nextToken());	//9.VOLUME
											
						try {
													
							pstmt.setString(1, ticker);System.out.println(date.toString());
							pstmt.setDate(2,new java.sql.Date(date.getTime()));
							pstmt.setDouble(3,openedPrice);
							pstmt.setDouble(4,highestPrice);
							pstmt.setDouble(5,lowestPrice);
							pstmt.setDouble(6,closedPrice);
							pstmt.setLong(7, volume);
							
							pstmt.execute();
						} catch(Exception e) {
							e.printStackTrace();
						}
						
						
											
						//System.out.println(stock.getCompany().getCode() + " " + stock.getTransactions().get(0).getDate()+ " " + stock.getTransactions().get(0).getClosePrice());
					}
					catch(Exception e){
						continue;
					}
				}
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		DbImporter importer = new DbImporter();
		
	
		importer.importTransaction("D:\\hose.txt");
		
		//importer.importDb();
		//importer.testDB();	
		//importer.importCompany("D:\\a.txt");
		
		//importer.testGetTransaction();
		
	}
	
	public void testDB(){
//		DataManager manager = null;
//		
//		try {
//		manager = new DataManager();
//		} catch (Exception e) {
//			// TODO: handle exception
//		}
//		Organization company = new Organization("ABC","DEF");		
//		
//		System.out.println("create");
//		try{
//		//manager.createCompany(company);
//		System.out.println("ok");
//		} catch (Exception e) {
//			
//			System.out.print("FAL");
//		}
	}
	
	public void importCompany(String fileName) {
		BufferedReader br = null;
		Connection connection = null;
		PreparedStatement pstmt = null;
		
		String username = "root";
		String password = "";
		String database = "effiport";
		ConnectionManager cm = new ConnectionManagerMySQL(database, username, password);
		connection = cm.getConnection();
				
		try {
			File file = new File(fileName);
			FileInputStream fileStream = new FileInputStream(file);
			InputStreamReader reader = new InputStreamReader(fileStream, "UTF8");
			br = new BufferedReader(reader);
		} catch (FileNotFoundException e1) {			
			e1.printStackTrace();
		} catch (UnsupportedEncodingException e) {			
			e.printStackTrace();
		}
		
		StringTokenizer tokenizer = null;
		String tmp = null;
		try {
			while((tmp = br.readLine()) != null){
				tokenizer = new StringTokenizer(tmp,"\t");
				
				String symbol = tokenizer.nextToken();
				
				String name = tokenizer.nextToken();
				
				String n1 = tokenizer.nextToken();
				n1 = "0";
				long listingVolume = Long.parseLong(n1);
				//tokenizer.nextToken();
				
				String n2 = tokenizer.nextToken();
				n2 = "0";
				long outstandingVolume = Long.parseLong(n2);
				//tokenizer.nextToken();
				
				java.util.Date date = null;
				DateFormat df = new SimpleDateFormat("dd/MM/yyyy");				
				try {
					date = df.parse(tokenizer.nextToken());
				} catch (ParseException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				
				//Create an Organization
				Organization org = new Organization(symbol, name);
				org.setListingVolume(listingVolume);
				org.setOutstandingVolume(outstandingVolume);
				org.setIpoDate(date);
				
//				DataManager manager = null;
//				try {
//					manager = new DataManager();
//				} catch (SQLException e1) {
//					// TODO Auto-generated catch block
//					e1.printStackTrace();
//				}
				
				try {													
					pstmt = connection.prepareStatement(CREATE_ORGANIZATION);
					pstmt.setString(1, org.getSymbol());
					pstmt.setString(2, org.getName());
					pstmt.setLong(3, org.getListingVolume());
					pstmt.setLong(4,org.getOutstingVolume());			
					pstmt.setDate(5, new java.sql.Date(org.getIpoDate().getTime()));						
					
					pstmt.execute();
					
					
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
					System.out.println(org.getSymbol());
				}
//				try{
//					manager.createOrganization(org);						
//					
//					System.out.print(i++);
//				} catch (Exception e) {					
//					System.out.print("FAL");
//				}				
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void testGetTransaction() {
		DataManager manager = null;
		try {
			manager = new DataManager();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		List<Transaction> trans = new ArrayList<Transaction>();
		trans = manager.getTransaction("ABT");
		int expected = 670;
		int actual = trans.size();
		
		System.out.println(actual);
		
	}
}
